package edu.stanford.travelhard;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;

public class DatabaseHelper {

	private static DatabaseHelper dbh;
	private static DatabaseOpenHelper dboh;
	
	/**
	 * Enum describing the SQLite media table
	 * @author nate
	 *
	 */
	public enum MediaColumn {
		ID ("id", "INTEGER PRIMARY KEY"),
		TRIP_ID ("tripID", "INTEGER"),
		MEDIA_TYPE ("mediaType", "INTEGER"),
		LATITUDE ("latitude", "REAL"),
		LONGITUDE ("longitude", "REAL"),
		ALTITUDE ("altitude", "REAL"),
		TIME_CAPTURED ("timeCaptured", "INTEGER"),
		TIME_PUBLISHED ("timePublished", "INTEGER"),
		AUDIO_FILENAME ("audioFilename", "TEXT"),
		PHOTO_FILENAME ("photoFilename", "TEXT"),
		PHOTO_ORIENTATION ("photoOrientation", "INTEGER");
		
		private final String columnName;
		private final String columnType;
		
		MediaColumn(String columnName, String columnType) {
			this.columnName = columnName;
			this.columnType = columnType;
		}
		
		public static final String CREATE_STATEMENT;
		
		static {
			String createStatement = "CREATE TABLE " + TravelHardConstants.DATABASE_TABLE_NAME + " (";
			for (int i = 0; i < MediaColumn.values().length; i++) {
				MediaColumn c = MediaColumn.values()[i];

				createStatement += c.columnName + " " + c.columnType;

				// don't want commas after last column
				if (i < MediaColumn.values().length - 1) {
					createStatement += ", ";
				}
			}
			createStatement += ");";

			CREATE_STATEMENT = createStatement;
		}
	};

	private Context context;
	private SQLiteDatabase db;

	public static DatabaseHelper getInstanceOf(Context c) {
		if (dbh == null) {
			dbh = new DatabaseHelper(c);
		}
		return dbh;
	}

	public DatabaseHelper(Context context) {
		this.context = context;
		this.dboh = new DatabaseOpenHelper(context);
	}

	public DatabaseHelper open() throws SQLException {
		db = dboh.getWritableDatabase();
		return this;
	}

	public void close() {
		dboh.close();
		db = null;
	}

	public long insert(Media m) {
		ContentValues values = new ContentValues();
		values.put(MediaColumn.TRIP_ID.columnName, m.getTripID());
		values.put(MediaColumn.MEDIA_TYPE.columnName, m.getType().ordinal());
		values.put(MediaColumn.LATITUDE.columnName, m.getLat());
		values.put(MediaColumn.LONGITUDE.columnName, m.getLng());
		values.put(MediaColumn.ALTITUDE.columnName, m.getAlt());
		values.put(MediaColumn.TIME_CAPTURED.columnName, m.getTimeCaptured().getTime());
		
		if (m.getTimePublished() != null) {
			values.put(MediaColumn.TIME_PUBLISHED.columnName, m.getTimePublished().getTime());
		} else {
			values.putNull(MediaColumn.TIME_PUBLISHED.columnName);
		}

		if (m.getAudioFilename() != null) {
			values.put(MediaColumn.AUDIO_FILENAME.columnName, m.getAudioFilename());
		} else {
			values.putNull(MediaColumn.AUDIO_FILENAME.columnName);
		}
		
		if (m.getPhotoFilename() != null) {
			values.put(MediaColumn.PHOTO_FILENAME.columnName, m.getPhotoFilename());
		} else {
			values.putNull(MediaColumn.PHOTO_FILENAME.columnName);
		}
		
		values.put(MediaColumn.PHOTO_ORIENTATION.columnName, m.getPhotoOrientation());

		Log.i("DB", "INSERT INTO media VALUES (" + values.toString() + ");");

		try {
			return db.insert(TravelHardConstants.DATABASE_TABLE_NAME, null, values);
		} catch (Exception e) {
			return -1;
		}
	}

	public List<Media> query() {
		return null;
	}

	public List<Media> selectAll() {
		Cursor cursor = this.db.query(TravelHardConstants.DATABASE_TABLE_NAME,
				null, null, null, null, null, MediaColumn.ID.columnName + " desc");
		return this.getMedia(cursor);
	}

	public List<Media> getUnpublished() {
		Cursor cursor = this.db.query(TravelHardConstants.DATABASE_TABLE_NAME,
				null, MediaColumn.TIME_PUBLISHED.columnName + " is null", null,
				null, null, MediaColumn.ID.columnName);
		return this.getMedia(cursor);
	}

	public void setPublished(Media m) {
		ContentValues values = new ContentValues();
		values.put(MediaColumn.TIME_PUBLISHED.columnName, m.getTimePublished().getTime());
		this.db.update(TravelHardConstants.DATABASE_TABLE_NAME, values,
				MediaColumn.ID.columnName + " = ?", new String[] { m.getDBID().toString() });
	}
	
	public void setAudioFile(Media m){
		ContentValues values = new ContentValues();
		values.put(MediaColumn.AUDIO_FILENAME.columnName, m.getAudioFilename());
		this.db.update(TravelHardConstants.DATABASE_TABLE_NAME, values,
				MediaColumn.ID.columnName + " = ?", new String[] { m.getDBID().toString() });
	}

	public void deleteMedia(long id) {
		this.db.delete(TravelHardConstants.DATABASE_TABLE_NAME,
				MediaColumn.ID.columnName + " = " + id, null);
	}
	
	public void deleteAll() {
		this.db.delete(TravelHardConstants.DATABASE_TABLE_NAME, null, null);
	}

	public void printAllEntries() {
		Cursor cursor = this.db.query(TravelHardConstants.DATABASE_TABLE_NAME, null, null, null, null, null, MediaColumn.ID.columnName);
		List<Media> media = this.getMedia(cursor);
		for (Media m : media) {
			Log.i("DB_ITEM", m.toString());
		}
	}

	private List<Media> getMedia(Cursor cursor) {
		List<Media> list = new ArrayList<Media>();
		if (cursor.moveToFirst()) {
			do {
				Media m = new Media(cursor.getLong(MediaColumn.ID.ordinal()),
						cursor.getLong(MediaColumn.TRIP_ID.ordinal()),
						MediaType.values()[cursor.getInt(MediaColumn.MEDIA_TYPE.ordinal())],
						cursor.getDouble(MediaColumn.LATITUDE.ordinal()),
						cursor.getDouble(MediaColumn.LONGITUDE.ordinal()),
						cursor.getDouble(MediaColumn.ALTITUDE.ordinal()),
						new Date(cursor.getLong(MediaColumn.TIME_CAPTURED.ordinal())),
						new Date(cursor.getLong(MediaColumn.TIME_PUBLISHED.ordinal())),
						cursor.getString(MediaColumn.AUDIO_FILENAME.ordinal()),
						cursor.getString(MediaColumn.PHOTO_FILENAME.ordinal()),
						cursor.getInt(MediaColumn.PHOTO_ORIENTATION.ordinal()));
				list.add(m);
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	class DatabaseOpenHelper extends SQLiteOpenHelper {

		public DatabaseOpenHelper(Context context) {
			super(context, TravelHardConstants.DATABASE_NAME, null, TravelHardConstants.DATABASE_VERSION);
		}

		public void onCreate(SQLiteDatabase db) {
			Log.i("DB", "Creating media table");
			db.execSQL(MediaColumn.CREATE_STATEMENT);
		}

		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + TravelHardConstants.DATABASE_TABLE_NAME);
			this.onCreate(db);
		}

	}
}
